package com.example.demo.DBmake;

import java.sql.*;
import java.util.ArrayList;

import com.example.demo.Encrypt.*;
import com.example.demo.JsonObj.ChatMessObj;
import com.example.demo.JsonObj.FriListObj;
import com.example.demo.JsonObj.FriendObj;

public class PostgreDB implements DateBaseImp{

    private Connection conn;
    private Statement st ;
    private String DBDriver = "org.postgresql.Driver";
    private String DBUrl = "jdbc:postgresql://localhost:5432/webtest";
    private String DBUser ="postgres";
    private String DBPwd ="6B59D859";
    public  PostgreDB(String _DBUrl,String _DBUser,String _DBPwd){
        DBUrl = _DBUrl;
        DBUser = _DBUser;
        DBPwd = _DBPwd;
    }

    public  PostgreDB(){

    }
    /***
     * 数据更新
     * @param sql 更新用sql语句
     * @return true = 更新成功
     *         false = 更新失败
     */
    @Override
    public boolean updateItem(String sql) {
        try {
            System.out.println("1");
            if(!DbConnect()) return false;
            st = conn.createStatement();
            st.executeUpdate(sql);
            System.out.println("2");
            if(!DisConnect()) return false;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
        return true;
    }

    /***
     * 验证驱动并连接数据库
     * @return true = 连接成功
     *         false = 验证或连接失败
     */
    @Override
    public boolean DbConnect() {
        try {
            Class.forName(DBDriver);
//            System.out.println(DBUrl+' '+DBUser+' '+DBPwd);
            conn = DriverManager.getConnection(DBUrl,DBUser,DBPwd);
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /***
     * 关闭连接
     * @return true  = 关闭成功
     *         false = 关闭失败
     */
    @Override
    public boolean DisConnect() {
        try{
            if(conn !=null && !conn.isClosed()) conn.close();
            if(st !=null) st.close();
        } catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
        return true;
    }

    /***
     * 验证密码
     * @param username 用户名
     * @param Pwd 用户加密后密码
     * @return true = 验证成功
     *         false = 验证失败
     */
    @Override
    public boolean CheckPassword(String username,String Pwd) {
        try {
            if(!DbConnect()) return false;
            st = conn.createStatement();
            String sql = "SELECT password FROM public.userinfo WHERE username=\'"+username+"\'";
            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                System.out.println(Pwd+' '+rs.getString(1));
                if(!Pwd.equals(rs.getString(1))) {
                    DisConnect();
                    return false;
                }
            }
            if(!DisConnect()) return false;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
        return true;
    }

    /***
     * 检查用户名
     * @param username 用户名
     * @return true = 用户名可用
     *         false = 用户名已被占用
     */
    @Override
    public boolean CheckUsername(String username) {
        try {
            if(!DbConnect()) return false;
            st = conn.createStatement();
            String sql = "SELECT uid FROM public.userinfo WHERE username=\'"+username+"\'";
            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                DisConnect();
                return false;
            }
            if(!DisConnect()) return false;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return false;
        }
        return true;
    }

    /***
     * 检查是否为首次登录
     * @param uid 用户uid
     * @return -1 == fail
     *          0 == N
     *          1 == Y
     */
    @Override
    public int CheckUserCom(int uid) {
        int cnt=0;
        try {
            if(!DbConnect()) return -1;
            st = conn.createStatement();
            String sql = "SELECT * FROM public.usercom WHERE uid=\'"+uid+"\'";
            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                cnt++;
            }
            if(!DisConnect()) return -1;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return -1;
        }
        return cnt == 0 ? 1 : 0;
    }

    /***
     * 获取用户盐值
     * @param username 用户名
     * @return String 盐值
     *          null 时表示获取失败
     */
    @Override
    public String GetSalt(String username) {
        String UserSalt = null;
        try {
            if(!DbConnect()) return null;
            st = conn.createStatement();
            String sql = "SELECT salt FROM public.userinfo WHERE username=\'"+username+"\'";
            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                UserSalt = rs.getString(1);
            }
            if(!DisConnect()) return null;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        return UserSalt;
    }

    /***
     * 获取用户昵称
     * @param uid 用户uid
     * @return String 昵称
     *          null 时表示获取失败
     */
    @Override
    public String GetNickname(int uid) {
        String nickname = null;
        try {
            if(!DbConnect()) return null;
            st = conn.createStatement();
            String sql = "SELECT nickname FROM public.usercom WHERE uid ="+ uid;
//            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                nickname = rs.getString(1);
            }
            if(!DisConnect()) return null;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        return nickname;
    }

    /***
     * 获取用户头像
     * @param uid
     * @return String 用户头像
     *         null 获取失败
     */
    @Override
    public String GetHeadPic(int uid) {
        String HeadPic = null;
        try {
            if(!DbConnect()) return null;
            st = conn.createStatement();
            String sql = "SELECT userimg FROM public.usercom WHERE uid ="+ uid;
//            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                HeadPic = rs.getString(1);
            }
            if(!DisConnect()) return null;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        return HeadPic;
    }

    /***
     * 获取用户名
     * @param Uid  用户Uid
     * @return String Username
     *         null fail
     */
    @Override
    public String GetUsername(int Uid) {
        String Username = null;
        try {
            if(!DbConnect()) return null;
            st = conn.createStatement();
            String sql = "SELECT username FROM public.userinfo WHERE uid ="+ Uid;
//            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                Username = rs.getString(1);
            }
            if(!DisConnect()) return null;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        return Username;
    }

    /***
     * 获取用户uid
     * @param username 用户名
     * @return  int 用户uid
     *          -1 时表示获取失败
     */
    @Override
    public int GetUid(String username) {
        int uid=-1;
        try {
            if(!DbConnect()) return -1;
            st = conn.createStatement();
            String sql = "SELECT uid FROM public.userinfo WHERE username=\'"+username+"\'";
//            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                uid = rs.getInt(1);
            }
            if(!DisConnect()) return -1;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return -1;
        }
        return uid;
    }

    /***
     * 获取用户uid
     * @param uid 用户uid
     * @return  String 用户好友列表(Json)
     *          null 时表示获取失败
     */
    @Override
    public String GetFriJson(int uid) {
        String FirendList=null;
        try {
            if(!DbConnect()) return null;
            st = conn.createStatement();
            String sql = "SELECT friend_list FROM public.usercom WHERE uid ="+ uid;
//            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                FirendList = rs.getString(1);
            }
            if(!DisConnect()) return null;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        return FirendList;
    }

    /***
     * 将FriendObj转为FriListObj
     * @param t  FriendObj
     * @param myUid 用户Uid
     * @return  FriListObj
     *          null 转换失败
     */
    @Override
    public FriListObj FRL2LFR(FriendObj t,int myUid) {
        FriListObj tmp = new FriListObj();
        tmp.setNickname(t.getNickname());
        tmp.setUid(t.getUid());
        int cnt=0;
        String last="";
        String Img="";
        int toUid = t.getUid();
        try {
            if(!DbConnect()) return null;
            st = conn.createStatement();
            String sql = "SELECT message FROM public.usercon WHERE (uid_to =" +toUid+" AND uid_from="+myUid+") OR (uid_to = "+myUid+" AND uid_from="+toUid+") ORDER BY m_time ASC";
//            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                last = rs.getString(1);
                cnt++;
            }
            System.out.println(1);
            sql = "SELECT userimg FROM public.usercom WHERE uid=" +toUid;
//            System.out.println(sql);
            rs = st.executeQuery(sql);
            while (rs.next()){
                Img = rs.getString(1);
            }
            System.out.println(2);
            if(!DisConnect()) return null;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        tmp.setCnt(cnt);
        tmp.setLast(last);
        tmp.setImg(Img);
        return tmp;
    }

    /***
     * 获取消息列表
     * @param myUid 用户uid
     * @param toUid 对象uid
     * @return ArrayList<ChatMessObj> 消息列表
     *         null 获取失败
     */
    @Override
    public ArrayList<ChatMessObj> GetMessList(int myUid, int toUid, String myNickname, String toNickname, String myHeadPic, String toHeadPic) {
        ArrayList<ChatMessObj> chatMessObjs = new ArrayList<>();
        try {
            if(!DbConnect()) return null;
            st = conn.createStatement();

            String sql = "SELECT message,uid_from FROM public.usercon WHERE (uid_to =" +toUid+" AND uid_from="+myUid+") OR (uid_to = "+myUid+" AND uid_from="+toUid+")  ORDER BY m_time ASC";
//            System.out.println(sql);
            ResultSet rs = st.executeQuery(sql);
            while (rs.next()){
                ChatMessObj tmp = new ChatMessObj();
                String message = rs.getString(1);
                int fromUid = rs.getInt(2);
                tmp.setMessage(message);
                if(fromUid == myUid) {
                    tmp.setRl(false);
                    tmp.setName(myNickname);
                    tmp.setImg(myHeadPic);
                }
                else {
                    tmp.setRl(true);
                    tmp.setName(toNickname);
                    tmp.setImg(toHeadPic);
                }
                chatMessObjs.add(tmp);
            }

            if(!DisConnect()) return null;
        }
        catch (SQLException throwables) {
            throwables.printStackTrace();
            return null;
        }
        return chatMessObjs;
    }


}
